package util;

import pojoCla.NewsChnl;
import pojoCla.SysUser;
import pojoCla.UserMsg;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *
 * @author zhazha
 *
 *
 *
 */
public class UserUtil {
    private DButil db;

    /**
     *
     * @param acct
     * @param pwd
     * @return user
     */
    //登录查询
    public SysUser cheLogin(String acct,String pwd)  {
        db = new DButil();
        SysUser user = null;
        String sql = "select * from sys_user where acct=? and pwd=?";
        Object[] obj = {acct,pwd};
        try {
            ResultSet query = db.query(sql, obj);
            if(query.next()){
                user = new SysUser();
                user.setAcct(query.getString("acct"));
                user.setSysCode(query.getString("sys_code"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
       return user;
    }
    //获取对应账号的栏目名
    public String getUserChnl(String acct){
        DButil db = new DButil();
        String sql = "select chnl_code from sys_user_chnl where acct=?";
        String code = "";
        Object[] obj = new Object[]{acct};
        try {
            ResultSet query = db.query(sql, obj);
            if(query.next()){
                code = query.getString("chnl_code");
            }
            if(!"".equals(code)){
                StringBuilder nameSql = new StringBuilder("select group_concat(name) from news_chnl where code in(");
                String[] split = code.split(",");
                for (int i = 0; i < split.length; i++) { //拼接sql
                    nameSql.append("?,");
                }
                nameSql.replace(nameSql.length()-1,nameSql.length(),")");
                ResultSet nameQuery = db.query(nameSql.toString(),split);
                if(nameQuery.next()){
                    code = nameQuery.getString(1);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return code;
    }
    //根据页面下标传回相应页数的数据
    public List<UserMsg> getPageUser(int pageIndex,int pageSize){
        db = new DButil();
        List<UserMsg> list = new ArrayList<>();
        //String sql = "select a.pwd,b.acct,group_concat(c.name) from sys_user a join sys_user_chnl b on b.acct=a.acct join news_chnl c on b.chnl_code=c.code group by b.acct limit ?,?";
        String sql = "select pwd,acct from sys_user limit ?,?";
        Object[] obj = {(pageIndex - 1) * pageSize,pageSize};
        try {
            ResultSet query = db.query(sql, obj);
            while (query.next()){
                String acct = query.getString("acct");
                String pwd = query.getString("pwd");
                UserMsg um = new UserMsg(acct,pwd,this.getUserChnl(acct));
                list.add(um);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return list;
    }
    //查询总共可以分成多少页
    public int getSumUserPage(int pageSize){
        db = new DButil();
        int sumUser = 0;
        int sumPage = 0;
        String sql = "select count(*) from sys_user";
        try {
            ResultSet query = db.query(sql,new Object[]{});
            if(query.next()){
                sumUser = query.getInt(1);
            }
            sumPage = sumUser % pageSize == 0 ?   sumUser / pageSize : sumUser / pageSize + 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return sumPage;
    }

    public boolean addUser(String acct,String pwd)  {
        db = new DButil();
        ChnlUtil cu = new ChnlUtil();
        try {
            //插入用户
            String sql = "insert into sys_user(acct,pwd,sys_code,crtime,uptime) values(?,?,?,?,?)";
            Timestamp time = new Timestamp(new Date().getTime());
            Object[] obj = {acct,pwd,1,time,time};
            db.update(sql,obj);
            //插入用户和栏目连接表数据
//            for(NewsChnl chnl: sumChnl){
//                String sql2 = "insert into sys_user_chnl(acct,chnl_code,crtime,uptime) values(?,?,?,?)";
//                Object[] obj2 = {acct,chnl.getCode(),time,time};
//                db.update(sql2,obj2);
//            }
            String selSql = "select group_concat(code) from news_chnl";
            obj = new Object[]{};
            String chnl_code = "";
            ResultSet query = db.query(selSql, obj);
            if(query.next()){
                chnl_code = query.getString(1);
            }
            String sql2 = "insert into sys_user_chnl(acct,chnl_code,crtime,uptime) values(?,?,?,?)";
            obj = new Object[]{acct,chnl_code,time,time};
            db.update(sql2,obj);
            db.submitUpdate();
        } catch (Exception e) {
            try {
                db.getConn().rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return false;
        }finally {
            db.closeConn();
        }
        return true;
    }
    public boolean delUser(String[] delAcct){
        //根据传进来的要删除的账号数组,拼接sql预编译语句
        db = new DButil();
        try {
            StringBuilder sql = new StringBuilder("delete a.*,b.* from sys_user a join sys_user_chnl b on a.acct=b.acct where a.acct in (");
            for (int i = 0; i < delAcct.length; i++) {
                sql.append("?,");
            }
            sql.replace(sql.length()-1,sql.length(),")");
            //由于参数是object数组,所以利用多态,直接把数据传进去
            db.update(sql.toString(), delAcct);
            db.submitUpdate();
        } catch (Exception e) {
            try {
                db.getConn().rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return false;
        } finally {
            db.closeConn();
        }
        return true;
    }

    public boolean editUser(String oldAcct,String acct,String pwd){
        db = new DButil();
        String sql = "update sys_user a,sys_user_chnl b set a.acct=?,a.pwd=?,b.acct=? where a.acct=b.acct and a.acct=?";
        Object[] obj = {acct,pwd,acct,oldAcct};
        try {
            db.update(sql,obj);
            db.submitUpdate();
        } catch (Exception e) {
            try {
                db.getConn().rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return false;
        } finally {
            db.closeConn();
        }
        return true;
    }
    public boolean accUser(String acct,String chnl)  {
        db = new DButil();
        if(!chnl.isEmpty()){
//            List<String> code = new ArrayList<>();
            try {
                String code = "";
                String[] split = chnl.split(","); //根据 , 拆分出chnlname,在查询出对应的code;
                StringBuilder selSql = new StringBuilder("select group_concat(code) from news_chnl where name in(");
                for (int i = 0; i < split.length; i++) {
//                    String selSql = "select code from news_chnl where name=?";
                    selSql.append("?,");
//                    Object[] obj = {split[i]};
                }
                selSql.replace(selSql.length()-1,selSql.length(),")");
                ResultSet query = db.query(selSql.toString(),split);
                if(query.next()){
                    code = query.getString(1);
                }
//                Object[] obj = {chnl};
//                ResultSet query = db.query(selSql, obj);
//                if(query.next()){
//                    code = query.getString(1);
//                }
//                String delSql = "delete chnl_code from sys_user_chnl where acct=?"; //先把原来的数据删除
//                Object[] obj = {acct};
//                db.update(delSql,obj);
                //开始插入数据
                Timestamp time = new Timestamp(new Date().getTime());
                String acSql = "update sys_user_chnl set chnl_code=?,uptime=? where acct=?";//更新数据
//                for(String chCode: code){
                    Object[] acObj = {code,time,acct};
                    db.update(acSql,acObj);
//                }
                db.submitUpdate();
            } catch (SQLException e) {
                try {
                    db.getConn().rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                return false;
            } finally {
                db.closeConn();
            }
            return true;
        }
        return false;
    }
    public List<UserMsg> selUser(String acct,int pageIndex,int pageSize){
        db = new DButil();
        List<UserMsg> user = new ArrayList<>();
//        String sql = "select a.pwd,b.acct,group_concat(c.name) from sys_user a join sys_user_chnl b on b.acct=a.acct join news_chnl c on b.chnl_code=c.code where a.acct like ? group by b.acct  limit ?,?";
//        Object[] obj = {"%"+acct+"%",(pageIndex - 1) * pageSize,pageSize};
        String sql = "select acct,pwd from sys_user where acct like ? limit ?,?";
        Object[] obj = {"%"+acct+"%",(pageIndex - 1) * pageSize,pageSize};
        try {
            ResultSet query = db.query(sql, obj);
            while (query.next()){
//                UserMsg um = new UserMsg(query.getString("acct"),query.getString("pwd"),query.getString("group_concat(c.name)"));
                UserMsg um = new UserMsg(query.getString("acct"),query.getString("pwd"),this.getUserChnl(query.getString("acct")));
                user.add(um);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return user;
    }
    public int getSelUserSumPgae(String acct,int pageSize){
        db = new DButil();
        int sumPage = 0;
        String sql = "select count(acct) from sys_user where acct like ?";
        Object[] obj = {"%"+acct+"%"};
        try {
            ResultSet query = db.query(sql, obj);
            if(query.next()){
                sumPage = query.getInt(1);
            }
            sumPage = sumPage % pageSize == 0 ?  sumPage / pageSize : sumPage / pageSize + 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return sumPage;
    }
    public String findUser(String acct){
        db = new DButil();
        String sql = "select * from sys_user where acct=?";
        String user = "";
        Object[] obj = {acct};
        try {
            ResultSet query = db.query(sql, obj);
            if(query.next()){
                user = query.getString("acct");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return user;
    }
}
